/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package net.wofly.right.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import net.wofly.right.domain.User;

public interface UserRepository
        extends PagingAndSortingRepository<User, String>, JpaSpecificationExecutor<User> {
    
	List<User> findByMobile(String mobile);

	User findByMobileAndPassword(String mobile, String password);

	User findByEmail(String email);
    
    List<User> findByOrganizationID(String organizationID);
    
    List<User> findByOrganizationIDAndStatus(String organizationID, Integer status);
    
    Page<User> findByOrganizationIDAndStatus(String organizationID, Integer status, Pageable pageable);
    
    List<User> findByMobileOrEmail(String mobile, String email);

    List<User> findByFollowUserID(String followUserID);
    
    List<User> findByNameLike(String name);
    
    List<User> findByNamePinYinLike(String namePinYin);
    
    List<User> findByNameContaining(String name);

    List<User> findByFollowUserIDAndStatus(String followUserID, Integer status);
    
    List<User> findByFollowUserIDAndUserID(String followUserID, String userID);
    
    List<User> findByFollowUserIDAndNameLike(String followUserID, String name);
    
    List<User> findByFollowUserIDAndNamePinYinLike(String followUserID, String namePinYin);
    
    @Query("select u from User u , Role r ,UserRole ur where u.userID = ur.userID and r.roleID = ur.roleID and (r.roleID = '106' or r.roleID = '107') ")
    List<User> findAllSales();
    
    
    @Query("select u ,ua from User u ,ReceivingAddr ua where u.userID=ua.userID and u.isLegal= ?1 ")
    List<User> findisLegal(Integer isLegal);
    
    @Query("select u from User u ,UserRole ur where u.userID = ur.userID and u.isLegal=1 and ur.roleID = ?1 ")
    List<User> getUserForOrder(String roleID);

    @Query(value = "SELECT " +
            "u.Userid AS userID, " +
            "u.Username AS userName, " +
            "u.Mobile AS mobile, " +
            "u.Name AS name, " +
            "u.Warehouseflag AS warehouseFlag, " +
            "u.Warehousetype AS warehouseType, " +
            "r.rolename AS roleName, " +
            "o.organizationname AS organizationName, " +
            "o.organizationid AS organizationID, " +
            "r.roleid AS roleID  " +
            "FROM " +
            "  sale_right_user AS u " +
            "LEFT JOIN sale_right_userrole AS ur ON u.Userid = ur.userid " +
            "LEFT JOIN sale_right_role AS r ON ur.roleid = r.roleid " +
            "LEFT JOIN sale_right_organization AS o ON u.Organizationid = o.organizationid " +
            "WHERE ur.roleid < 200 " +
            "AND u.status = 1 " +
            "AND IF(:name IS NULL, 1=1, u.Name LIKE '%:name%')" +
            "AND IF(:mobile IS NULL, 1=1, u.Mobile LIKE '%:mobile%')" +
            "AND IF(:roleID IS NULL, 1=1, ur.roleid = :roleID)" +
            "AND IF(:organizationID IS NULL, 1=1, u.Organizationid = :organizationID)" +
            "ORDER BY u.modifyDate desc " +
            "LIMIT :startRow, :pageSize", nativeQuery = true)
    List<List<Object>> findAllUser(@Param(value = "name") String name, @Param(value = "mobile") String mobile,
                                   @Param(value = "roleID") String roleID, @Param(value = "organizationID") String organizationID,
                                   @Param(value = "startRow") Integer startRow, @Param(value = "pageSize") Integer pageSize);

    @Query(value = "SELECT " +
            "COUNT(u.userid) " +
            "FROM " +
            "  sale_right_user AS u " +
            "LEFT JOIN sale_right_userrole AS ur ON u.Userid = ur.userid " +
            "LEFT JOIN sale_right_role AS r ON ur.roleid = r.roleid " +
            "LEFT JOIN sale_right_organization AS o ON u.Organizationid = o.organizationid " +
            "WHERE ur.roleid < 200 " +
            "AND u.status = 1 " +
            "AND IF(:name IS NULL, 1=1, u.Name LIKE '%:name%')" +
            "AND IF(:mobile IS NULL, 1=1, u.Mobile LIKE '%:mobile%')" +
            "AND IF(:roleID IS NULL, 1=1, ur.roleid = :roleID)" +
            "AND IF(:organizationID IS NULL, 1=1, u.Organizationid = :organizationID)" +
            "ORDER BY ur.roleid ", nativeQuery = true)
    Long countAllUser(@Param(value = "name") String name, @Param(value = "mobile") String mobile,
                      @Param(value = "roleID") String roleID, @Param(value = "organizationID") String organizationID);

}